package jdbc;
/*
 * create hh_acounts(id number(11) prmary key, amount number(6,2));
 * insert into hh_acounts (1,9999);
 * insert into hhh_acounts(2,8888);
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TransactionDemo {
	private static final String SELECT="select * from hh_emp where id=?";
	private static final String DELETE="delete from hh_emp where id=?";
	private static final String INSERT="insert into hh_emp(id,name) values(?,?)";
	public static void main(String[] args) throws Exception {
//		transactionTest(1, "heantai");
		transfer(1,2,1000.0);
	}

	private static void transfer(int fromId, int toId, double money) throws Exception {
		Connection con=null;
		try {
			con=DBUtil.openInThread();
			con.setAutoCommit(false);
			updateFromAccout(fromId,money);
			updateToAccount(toId,money);
			con.commit();
		} catch (Exception e) {
			con.rollback();
			throw e;
		}finally{
			DBUtil.closeInThread();
		}
	}

	private static void updateToAccount(int toId, double money) throws Exception {
		String sql="update hh_acounts set amount=amount+? where id=?";
		PreparedStatement pst=null;
		try {
			Connection con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setDouble(1,money);
			pst.setInt(2, toId);
			pst.executeUpdate();
		}finally{
			DBUtil.close(null, pst, null);
		}
	}

	private static void updateFromAccout(int fromId, double money) throws Exception {
		String sql="update hh_acounts set amount=amount-? where id=?";
		PreparedStatement pst=null;
		try {
			Connection con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setDouble(1,money);
			pst.setInt(2, fromId);
			pst.executeUpdate();
		} finally{
			DBUtil.close(null,pst,null);
		}
	}

	private static void transactionTest(int id,String name) throws Exception {
		//查询
		//删除
		//insert
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs= null;
		try{
			con=DBUtil.open();
			con.setAutoCommit(false);
			pst=con.prepareStatement(SELECT);
			pst.setInt(1, id);
			rs=pst.executeQuery();
			if(rs.next()){
				DBUtil.close(null, pst, null);
				pst=con.prepareStatement(DELETE);
				pst.setInt(1, id);
				pst.executeUpdate();
				DBUtil.close(null, pst, null);
			}
			DBUtil.close(null, null, rs);
			pst=con.prepareStatement(INSERT);
			pst.setInt(1, id);
			pst.setString(2, name);
			pst.executeUpdate();
			con.commit();
		}catch(Exception e ){
			con.rollback();
			throw e;
		}finally{
			DBUtil.close(con, pst, null);
		}
	}
}
